﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Data;
using Restaurant.DTO;

namespace Restaurant.DAO
{
    public class NhapHangDB
    {
        private SqlConnection conn = KetNoiDB.TaoKetNoi();
        public DataTable LayNhapHang()
        {
            try
            {
                
                conn.Open();
                string strCmd = "dbo.spLayNhapHang";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                conn.Close();
                return dbtab;
            }
            catch(Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }

        public DataTable LayNhapHangTheoMa(int maNH)
        {
            try
            {
                
                conn.Open();
                string strCmd = "dbo.spLayNhapHangTheoMa";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", maNH));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw;
            }
        }

        public bool ThemNhapHang(NhapHang nh)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spThemNhapHang";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ngayNhap", nh.NGAYNHAP));
                cmd.Parameters.Add(new SqlParameter("@tongTien", nh.TONGTIEN));
                cmd.Parameters.Add(new SqlParameter("@maKho", nh.MAKHO));
                cmd.Parameters.Add(new SqlParameter("@maHTThanhToan", nh.MAHTTOAN));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", nh.TINHTRANG));
                cmd.Parameters.Add(new SqlParameter("@maNCC", nh.MANCC));
                cmd.Parameters.Add(new SqlParameter("@ngayThanhToan", nh.NGAYTHANHTOAN));
                //cmd.Parameters.Add(new SqlParameter("@dbCTNhap", nh.CHITIETNHAP));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }

        public bool SuaNhapHang(NhapHang nh)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spSuaNhapHang";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", nh.MANHAPHANG));
                cmd.Parameters.Add(new SqlParameter("@ngayNhap", nh.NGAYNHAP));
                cmd.Parameters.Add(new SqlParameter("@tongTien", nh.TONGTIEN));
                cmd.Parameters.Add(new SqlParameter("@maKho", nh.MAKHO));
                cmd.Parameters.Add(new SqlParameter("@maHTThanhToan", nh.MAHTTOAN));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", nh.TINHTRANG));
                cmd.Parameters.Add(new SqlParameter("@maNCC", nh.MANCC));
                cmd.Parameters.Add(new SqlParameter("@ngayThanhToan", nh.NGAYTHANHTOAN));
                cmd.Parameters.Add(new SqlParameter("@dbCTNhap", nh.CHITIETNHAP));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }

        public bool XoaNhapHang(int ma)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spXoaNhapHang";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", ma));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }

        public int TaoMaNhapHang()
        {
            string strcmd = "select dbo.funTaoMaNhapHang()";
            SqlCommand cmd = new SqlCommand(strcmd, conn);
            conn.Open();
            SqlDataReader dataReaderSql = cmd.ExecuteReader();
            int retu = -1;
            while (dataReaderSql.Read())
            {
                retu = int.Parse(dataReaderSql[0].ToString());
                break;
            }
            dataReaderSql.Close();
            //conn.Dispose();
            conn.Close();
            return retu;
        }

        public bool ThemCTNhapHang(int maNhapHang, int maNL, float soLuong, int donGia, int tong, int maKho)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spThemCTNhapHang";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", maNhapHang));
                cmd.Parameters.Add(new SqlParameter("@maNL", maNL));
                cmd.Parameters.Add(new SqlParameter("@soLuong", soLuong));
                cmd.Parameters.Add(new SqlParameter("@donGia", donGia));
                cmd.Parameters.Add(new SqlParameter("@tong", tong));
                cmd.Parameters.Add(new SqlParameter("@maKho", maKho));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }

        public bool XoaCTNhapHang(int maNhapHang)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spXoaCTNhapHang";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", maNhapHang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }

        public bool ThemCTNhapHangDeadLockError(int maNhapHang, int maNL, float soLuong, int donGia, int tong, int maKho)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spDeadlock_ThemCTNhapHang_Error";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", maNhapHang));
                cmd.Parameters.Add(new SqlParameter("@maNL", maNL));
                cmd.Parameters.Add(new SqlParameter("@soLuong", soLuong));
                cmd.Parameters.Add(new SqlParameter("@donGia", donGia));
                cmd.Parameters.Add(new SqlParameter("@tong", tong));
                cmd.Parameters.Add(new SqlParameter("@maKho", maKho));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }

        public bool ThemCTNhapHangDeadLock(int maNhapHang, int maNL, float soLuong, int donGia, int tong, int maKho)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spDeadlock_ThemCTNhapHang_Fixed";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNhapHang", maNhapHang));
                cmd.Parameters.Add(new SqlParameter("@maNL", maNL));
                cmd.Parameters.Add(new SqlParameter("@soLuong", soLuong));
                cmd.Parameters.Add(new SqlParameter("@donGia", donGia));
                cmd.Parameters.Add(new SqlParameter("@tong", tong));
                cmd.Parameters.Add(new SqlParameter("@maKho", maKho));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
        }
    }
}
